﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.OleDb;
namespace BiaoQianPrint.Excel
{
    class ExcelImportImpl : IExcelImport
    {
        public enum ExceclType { Excecl2003, Excecl2007, Excecl2000 };
        //文件路径。
        private string _FilePath;
        public string FilePath{
            set { _FilePath = value; }
        }

        private string _SheetName;

        public DataTable getDataTableFromExcel(string pSheetName)
        {
            _SheetName = pSheetName;


            DataTable dt = new DataTable();
            try
            {
                //string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + strPath+ ";Extended Properties='Excel 12.0; HDR=NO; IMEX=1'"; //此连接可以操作.xls与.xlsx文件

                //   string strCon = "Provider=Microsoft.Jet.OLEDB.12.0;" + "Data Source=" + strPath + ";" + "Extended Properties='Excel 12.0;HDR=NO;IMEX=2;'";
                //string strCon = getStrCon(strPath, ExceclType.Excecl2003);// "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=2;'";

                string strCon = getStrCon(_FilePath, ExceclType.Excecl2007);
                
                using (OleDbConnection con = new OleDbConnection(strCon))
                {
                    con.Open();
                    System.Data.DataTable dtbl1 = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                }
                String strCmd = "select * from [" + _SheetName + "A1:CS10000]";
                OleDbDataAdapter cmd = new OleDbDataAdapter(strCmd, strCon);
                cmd.Fill(dt);
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show(ex.Message); 
                System.Windows.Forms.MessageBox.Show("读取数据失败");
                return null;
                
            }
            return dt;
        }
        public DataTable GetSheetList(string strPath)
        {
            _FilePath = strPath;

            System.Data.DataTable dtbl = new System.Data.DataTable();
            string strConn2007 = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + strPath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'";
            using (OleDbConnection con = new OleDbConnection(strConn2007))
            {
                con.Open();
                dtbl = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            }
            return dtbl;
        }
        private static string getStrCon(string strPath, ExceclType type)
        {
            string strConn2007 = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + strPath + ";Extended Properties='Excel 12.0; HDR=YES; IMEX=1'"; //此连接可以操作.xls与.xlsx文件

            //   string strCon = "Provider=Microsoft.Jet.OLEDB.12.0;" + "Data Source=" + strPath + ";" + "Extended Properties='Excel 12.0;HDR=NO;IMEX=2;'";
            //string strCon2003 = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strPath + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
            string strCon2003 = string.Format(@"Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = {0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';", strPath);

            if (type == ExceclType.Excecl2003)
            {
                return strCon2003;
            }
            else // if (type == ExceclType.Excecl2007)
            {
                return strConn2007;
            }
        }
    }
}
